"""
Case Type   : explain
Case Name   : explain使用参数plan,参数缺省值
Create At   : 2021/12/13
Owner       : opentestcase004
Description :
    1.建表
    2.向表中插入数据
    3.explain使用参数plan,参数缺省值;查询系统表plan_table数据
    4.清理环境
Expect      :
    1.建表成功
    2.向表中插入数据成功
    3.不打印执行信息,返回EXPLAIN SUCCESS;plan_table表中查询到update操作
    4.清理环境成功
History     :
    modified by opentestcase036 at 2022/05/30:优化用例,在同一个session执行explain操作
    和查询系统表plan_table操作
"""

import os
import unittest
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class SQL(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.table = 'explain_param_plan_0045'
        self.plan_table = 'plan_table'
        self.Constant = Constant()

    def test_explain(self):
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        step = 'step1:建表 expect:建表成功'
        self.logger.info(step)
        create_table = self.primary_sh.execut_db_sql(f'''
            drop table if exists {self.table};
            create table {self.table}(col1 int,col2 int);''')
        self.logger.info(create_table)
        self.assertIn(self.Constant.CREATE_TABLE_SUCCESS, create_table,
                      "建表失败" + step)

        step = 'step2:向表中插入数据 expect:向表中插入数据成功'
        self.logger.info(step)
        insert_data = self.primary_sh.execut_db_sql(f'''
            insert into {self.table} values(1,1),(2,2);''')
        self.logger.info(insert_data)
        self.assertIn(self.Constant.INSERT_SUCCESS_MSG, insert_data,
                      "向表中插入数据失败" + step)

        step = 'step3:explain使用参数plan,参数为缺省值;'\
               '查询系统表plan_table数据' \
               'expect:不打印执行信息,返回EXPLAIN SUCCESS;' \
               '系统表plan_table中查询到update操作'
        self.logger.info(step)
        explain = self.primary_sh.execut_db_sql(f'''
            explain (plan) update {self.table} set col1 = 5;\
            select * from  {self.plan_table};''')
        self.logger.info(explain)
        self.assertIn('EXPLAIN SUCCESS', explain, "打印信息不正确" + step)
        self.assertIn(' UPDATE', explain,
                      "explain使用plan参数执行结果不正确" + step)

    def tearDown(self):
        step = 'step4:清理环境 expect:清理环境成功'
        self.logger.info(step)
        de_table = self.primary_sh.execut_db_sql(f'''
            drop table {self.table};''')
        self.logger.info(de_table)
        self.assertIn(self.Constant.DROP_TABLE_SUCCESS, de_table,
                      "执行失败" + step)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
